﻿using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Text;
using System.Web;
using Framework;

namespace Enterprise
{
    public class DownloadService : BaseService
    {
        /// <summary>
        /// 添加下载资源
        /// </summary>
        /// <param name="name">名称</param>
        /// <param name="nameEn">名称(En)</param>
        /// <param name="description">描述</param>
        /// <param name="descriptionEn">描述(En)</param>
        /// <param name="type">类型</param>
        /// <param name="language">语言</param>
        /// <param name="sort">排序</param>
        /// <param name="filedata">文件</param>
        /// <returns></returns>
        public int Add(string name, string nameEn, string description, string descriptionEn, int type, string language, int sort, HttpPostedFileBase filedata)
        {
            Check.Argument.IsNotOutOfLength(name, 50, "资源名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "资源名称(En)");
            var ext = Path.GetExtension(filedata.FileName);

            var uploadHelper = new UploadHelper
            {
                PostedFile = filedata,
                RelativePath = "~/Upload/Download/",
                IsImage = false,
                FileMaxLength = 1024 * 1024 * 200,
            };
            if (!uploadHelper.Save())
            {
                throw new Exception(uploadHelper.ErrorMessage);
            }
            var filePath = uploadHelper.FilePhyPath;
            var fileName = uploadHelper.FileName;
            var sb = new StringBuilder();
            sb.Append("insert into TbDownload(");
            sb.Append("CoName,CoType,CoTypeName,CoUrl,CoDate,CoDescription,CoLanguage,CoNameEn,CoDescriptionEn,CoTypeNameEn,CoFileName,CoPath,CoSort");
            sb.Append(") values(");
            sb.Append("@CoName,@CoType,@CoTypeName,@CoUrl,@CoDate,@CoDescription,@CoLanguage,@CoNameEn,@CoDescriptionEn,@CoTypeNameEn,@CoFileName,@CoPath,@CoSort");
            sb.Append(");select last_insert_rowid() from TbDownload");
            var parameter = new[]
                                {
                                    new SQLiteParameter("@CoName", DbType.String, 255),
                                    new SQLiteParameter("@CoType", DbType.String, 50),
                                    new SQLiteParameter("@CoTypeName", DbType.String, 255),
                                    new SQLiteParameter("@CoUrl", DbType.String, 255),
                                    new SQLiteParameter("@CoDate", DbType.DateTime),
                                    new SQLiteParameter("@CoDescription", DbType.String),
                                    new SQLiteParameter("@CoLanguage", DbType.String, 10),
                                    new SQLiteParameter("@CoNameEn", DbType.String, 255),
                                    new SQLiteParameter("@CoDescriptionEn", DbType.String),
                                    new SQLiteParameter("@CoTypeNameEn", DbType.String, 255),
                                    new SQLiteParameter("@CoFileName", DbType.String, 255),
                                    new SQLiteParameter("@CoPath", DbType.String, 255),
                                    new SQLiteParameter("@CoSort", DbType.Int32),
                                    new SQLiteParameter("@CoExtensionName", DbType.String, 20)

                                };
            parameter[0].Value = name;
            parameter[1].Value = type;
            parameter[2].Value = "";
            parameter[3].Value = "";
            parameter[4].Value = DateTime.Now;
            parameter[5].Value = description;
            parameter[6].Value = language;
            parameter[7].Value = nameEn;
            parameter[8].Value = descriptionEn;
            parameter[9].Value = "";
            parameter[10].Value = fileName;
            parameter[11].Value = filePath;
            parameter[12].Value = sort;
            parameter[13].Value = ext;
            var rows = DbHelperSqLite.GetSingle(sb.ToString(), parameter).SafeToInt();
            if (rows > 0)
            {
                return rows;
            }
            throw new Exception("添加失败！");
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="id">ID</param>
        /// <param name="name">名称</param>
        /// <param name="nameEn">名称(En)</param>
        /// <param name="description">描述</param>
        /// <param name="descriptionEn">描述(En)</param>
        /// <param name="type">类型</param>
        /// <param name="language">语言</param>
        /// <param name="sort">排序</param>
        /// <returns></returns>
        public int Eidt(int id, string name, string nameEn, string description, string descriptionEn, int type, string language, int sort)
        {
            Check.Argument.IsNotOutOfLength(name, 50, "图片名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "图片名称(En)");
            var sb = new StringBuilder();
            sb.Append("update TbDownload set ");
            sb.Append(" CoName=@CoName,");
            sb.Append(" CoDescription=@CoDescription,");
            sb.Append(" CoLanguage=@CoLanguage,");
            sb.Append(" CoNameEn=@CoNameEn,");
            sb.Append(" CoDescriptionEn=@CoDescriptionEn,");
            sb.Append(" CoType=@CoType,");
            sb.Append(" CoTypeName=@CoTypeName,");
            sb.Append(" CoTypeNameEn=@CoTypeNameEn,");
            sb.Append(" CoSort=@CoSort");
            sb.Append(" where CoDownloadId=@id");
            var parameter = new[]
                                    {
                                        new SQLiteParameter("@CoName", DbType.String, 255),
                                        new SQLiteParameter("@CoDescription", DbType.String),
                                        new SQLiteParameter("@CoLanguage", DbType.String, 10),
                                        new SQLiteParameter("@CoNameEn", DbType.String, 255),
                                        new SQLiteParameter("@CoDescriptionEn", DbType.String),
                                        new SQLiteParameter("@CoType", DbType.Int32),
                                        new SQLiteParameter("@CoTypeName", DbType.String, 255),
                                        new SQLiteParameter("@CoTypeNameEn", DbType.String, 255),
                                        new SQLiteParameter("@id", DbType.Int32),
                                             new SQLiteParameter("@CoSort", DbType.Int32)

                                    };
            parameter[0].Value = name;
            parameter[1].Value = description;
            parameter[2].Value = language;
            parameter[3].Value = nameEn;
            parameter[4].Value = descriptionEn;
            parameter[5].Value = type;
            parameter[6].Value = "";
            parameter[7].Value = "";
            parameter[8].Value = id;
            parameter[9].Value = sort;
            var rows = DbHelperSqLite.ExecuteSql(sb.ToString(), parameter).SafeToInt();
            if (rows > 0)
            {
                return rows;
            }
            throw new Exception("修改失败！");
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id">ID</param>
        public void Delete(int id)
        {
            var path = HttpContext.Current.Server.MapPath("~/Upload/Download/");
            var sb = new StringBuilder();
            sb.Append("select * from TbDownload where CoDownloadId=@id");
            var parameter = new[]
                                {
                                    new SQLiteParameter("@id", DbType.Int32)
                                };
            parameter[0].Value = id;
            var ds = DbHelperSqLite.Query(sb.ToString(), parameter);
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoFileName"].SafeToString()));
            }
            var sbTwo = new StringBuilder();
            sbTwo.Append("delete  from TbDownload where CoDownloadId=@id");
            var paramTwo = new[]
                                {
                                    new SQLiteParameter("@id", DbType.Int32)
                                };
            paramTwo[0].Value = id;
            int rows = DbHelperSqLite.ExecuteSql(sbTwo.ToString(), paramTwo);
            if (!(rows > 0))
                throw new Exception("记录不存在！");
        }
        /// <summary>
        /// 删除图片
        /// </summary>
        /// <param name="id">ID</param>
        /// <returns></returns>
        public int DeletePicture(int id)
        {
            var path = HttpContext.Current.Server.MapPath("~/Upload/Download/");
            var sb = new StringBuilder();
            sb.Append("select * from TbDownload where CoDownloadId=@id");
            var parameter = new[]
                                {
                                    new SQLiteParameter("@id", DbType.Int32)
                                };
            parameter[0].Value = id;
            var ds = DbHelperSqLite.Query(sb.ToString(), parameter);
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoFileName"].SafeToString()));
            }
            var sbTwo = new StringBuilder();
            sbTwo.Append("update TbDownload set CoFileName=@CoFileName where CoDownloadId=@id");
            var paramTwo = new[]
                                {
                                    new SQLiteParameter("@id", DbType.Int32),
                                    new SQLiteParameter("@CoFileName", DbType.String, 255)
                                };
            paramTwo[0].Value = id;
            paramTwo[1].Value = "";
            int rows = DbHelperSqLite.ExecuteSql(sbTwo.ToString(), paramTwo);
            return rows;
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns>Table 返回一张表格</returns>
        public Table GetTable(string name, int pageValue, int rowsValue)
        {
            var strWhere = new StringBuilder();
            strWhere.Append(" where CoName like '%'||@CoName||'%' escape '\\' or  CoNameEn like '%'||@CoName||'%' escape '\\' ");
            var parameter = new[]
                           {
                           
                               new SQLiteParameter("@startIndex", DbType.Int32),
                               new SQLiteParameter("@endIndex", DbType.Int32),   
                               new SQLiteParameter("@CoName", DbType.String, 255)
                           };
            parameter[0].Value = pageValue > 0 ? (pageValue - 1) * rowsValue : 0;
            parameter[1].Value = rowsValue;
            parameter[2].Value = name.SqliteLike();
            var sqlOne = new StringBuilder();
            sqlOne.Append("select count(*) from TbDownload ");
            sqlOne.Append(strWhere);
            int total = DbHelperSqLite.GetSingle(sqlOne.ToString(), parameter).SafeToInt();
            var sqlTwo = new StringBuilder();
            sqlTwo.Append("select * from TbDownload ");
            sqlTwo.Append(strWhere);
            sqlTwo.Append(" order by codownloadid  ");
            sqlTwo.Append(" LIMIT @endIndex OFFSET @startIndex ");
            var ds = DbHelperSqLite.Query(sqlTwo.ToString(), parameter);
            var table = new Table
               {
                   Rows = ds.Tables["ds"].Rows,
                   Total = total
               };
            return table;
        }

        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id">获取ID</param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {

            SQLiteParameter[] parameters =
                {
                    new SQLiteParameter("@id", DbType.Int32)
                };
            parameters[0].Value = id;
            return DbHelperSqLite.Query("select *  from TbDownload  where CoDownloadId=@id", parameters).Tables["ds"].Rows[0];

        }
    }
}

